﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Reflection;
using DotNetDal.Core.StructureAttributes;
using DotNetDal.Core.Data;
using System.Data.SqlClient;
using System.Data;

namespace DotNetDal.Core.Data
{
    public class DALRecord
    {
        #region Private Static Fields
        private static Dictionary<string, Dictionary<string, Type>> _fieldTypes = new Dictionary<string, Dictionary<string, Type>>();
        private static Dictionary<string, Dictionary<string, DbType>> _dbTypes = new Dictionary<string, Dictionary<string, DbType>>();
        private static Dictionary<string, Dictionary<string, int>> _fieldLengths = new Dictionary<string, Dictionary<string, int>>();
        private static Dictionary<string, List<string>> _fieldNames = new Dictionary<string, List<string>>();
        private static Dictionary<string, List<string>> _primaryKeys = new Dictionary<string, List<string>>();
        private static Dictionary<string, List<string>> _autoNumbers = new Dictionary<string, List<string>>();
        private static Dictionary<string, List<string>> _nullableFields = new Dictionary<string, List<string>>();
        private static Dictionary<string, string> _schema = new Dictionary<string, string>();
        #endregion

        public DALRecord()
        {
            this.RetrieveFromDatabase = false;

            this._className = this.GetType().Name;
            this._initializeType();
            this._intializeInstance();
        }

        #region Internal Fields
        internal bool RetrieveFromDatabase;
        internal FieldList Fields;
        internal List<string> FieldNames
        {
            get
            {
                return _fieldNames[this.ClassName];
            }
        }
        internal string SchemaName
        {
            get
            {
                return _schema[this.ClassName];
            }
        }
        internal string ClassName
        {
            get
            {
                return this._className;
            }
        }
        internal string ClassNameWithSchemaName
        {
            get
            {
                return SchemaName + "." + ClassName;
            }
        }
        #endregion

        #region Private Fields
        private string _className;
        private Dictionary<string, Type> fieldTypes
        {
            get
            {
                if (!DALRecord._fieldTypes.ContainsKey(this.ClassName))
                    DALRecord._fieldTypes.Add(this.ClassName, new Dictionary<string, Type>());

                return DALRecord._fieldTypes[this.ClassName];
            }
        }
        private Dictionary<string, DbType> dbTypes
        {
            get
            {
                if (!DALRecord._dbTypes.ContainsKey(this.ClassName))
                    DALRecord._dbTypes.Add(this.ClassName, new Dictionary<string, DbType>());

                return DALRecord._dbTypes[this.ClassName];
            }
        }
        private Dictionary<string, int> fieldLengths
        {
            get
            {
                if (!DALRecord._fieldLengths.ContainsKey(this.ClassName))
                    DALRecord._fieldLengths.Add(this.ClassName, new Dictionary<string, int>());

                return DALRecord._fieldLengths[this.ClassName];
            }
        }
        private List<string> fieldNames
        {
            get
            {
                if (!DALRecord._fieldNames.ContainsKey(this.ClassName))
                    DALRecord._fieldNames.Add(this.ClassName, new List<string>());

                return DALRecord._fieldNames[this.ClassName];
            }
        }
        private List<string> primaryKeys
        {
            get
            {
                if (!DALRecord._primaryKeys.ContainsKey(this.ClassName))
                    DALRecord._primaryKeys.Add(this.ClassName, new List<string>());

                return DALRecord._primaryKeys[this.ClassName];
            }
        }
        private List<string> autoNumbers
        {
            get
            {
                if (!DALRecord._autoNumbers.ContainsKey(this.ClassName))
                    DALRecord._autoNumbers.Add(this.ClassName, new List<string>());

                return DALRecord._autoNumbers[this.ClassName];
            }
        }
        private List<string> nullableFields
        {
            get
            {
                if (!DALRecord._nullableFields.ContainsKey(this.ClassName))
                    DALRecord._nullableFields.Add(this.ClassName, new List<string>());

                return DALRecord._nullableFields[this.ClassName];
            }
        }
        #endregion

        public object this[string fieldName]
        {
            get
            {
                return this.Fields[fieldName];
            }
            set
            {
                this.Fields[fieldName] = value;
            }
        }

        public static T[] GetFromSql<T>(string sql) where T : DALRecord, new()
        {
            return GetFromSql<T>(sql, new SqlParameter[] { });
        }
        public static T[] GetFromSql<T>(string sql, SqlParameter[] sqlParams) where T : DALRecord, new()
        {
            List<T> collection = new List<T>();

            using (SqlDataReader reader = SqlServer.GetDataReader(sql, sqlParams))
            {
                while (reader.Read())
                {
                    T record = new T();
                    record.RetrieveFromDatabase = true;

                    List<string> fieldList = _retrieveFieldList(reader);

                    foreach (string fieldName in fieldList)
                    {
                        if (record.Fields.ContainsKey(fieldName))
                        {
                            record[fieldName] = reader.GetValue(reader.GetOrdinal(fieldName));

                            if (record[fieldName] == DBNull.Value)
                                record[fieldName] = null;
                        }
                        else
                        {
                            record[fieldName] = null;
                        }
                    }

                    collection.Add(record);
                }
            }

            return collection.ToArray();
        }
        public static T[] RetrieveByFieldValues<T>(T instance, string[] fieldNames, string[] orderByFields, object[][] values) where T : DALTable, new()
        {
            string whereSql = "";
            string sql = @"select * from [" + instance.SchemaName + "].[" + instance.ClassName + @"]";

            sql += " where ";

            List<SqlParameter> sqlParams = new List<SqlParameter>();

            for (int i = 0; i < values.Length; i++)
            {
                for (int j = 0; j < fieldNames.Length; j++)
                {
                    string fieldName = fieldNames[j];
                    object fieldValue = values[i][j];

                    whereSql += (whereSql != "" ? " and " : "") + "[" + fieldName + "] = @" + fieldName; 
                    sqlParams.Add(new SqlParameter("@" + fieldName, fieldValue));
                }
            }

            return GetFromSql<T>(sql + " " + whereSql);
        }

        #region Internal Methods
        internal bool IsAutoNumber(string fieldName)
        {
            return autoNumbers.Contains(fieldName);
        }
        internal bool IsPrimaryKey(string fieldName)
        {
            return primaryKeys.Contains(fieldName);
        }
        internal bool IsNullable(string fieldName)
        {
            return nullableFields.Contains(fieldName);
        }
        internal DbType GetDatabaseType(string fieldName)
        {
            return dbTypes[fieldName];
        }
        internal static List<string> _retrieveFieldList(SqlDataReader reader)
        {
            List<string> list = new List<string>();

            foreach (DataRow row in reader.GetSchemaTable().Rows)
                list.Add(row["ColumnName"].ToString());

            return list;
        }
        #endregion

        #region Private Methods
        private void _intializeInstance()
        {
            // Populate fields
            Fields = new FieldList();

            foreach (string fieldName in _fieldNames[this.ClassName])
                Fields.Add(fieldName, null);
        }
        private void _initializeType()
        {
            //if we have already stored the Metadata about the class then save a lot of expensive reflection
            if (_schema.ContainsKey(_className))
            {
                return;
            }
            else
            {
                _schema.Add(_className, null);

                Type dataClassType = this.GetType();

                if (dataClassType.GetCustomAttributes(typeof(TableAttribute), true).Length > 0)
                    _schema[_className] = ((TableAttribute)dataClassType.GetCustomAttributes(typeof(TableAttribute), true)[0]).Schema;

                foreach (PropertyInfo pi in dataClassType.GetProperties())
                {
                    if (pi.GetCustomAttributes(typeof(FieldAttribute), true).Length > 0)
                    {
                        FieldAttribute fatt = (FieldAttribute)pi.GetCustomAttributes(typeof(FieldAttribute), true)[0];

                        //add field name
                        if (!fieldNames.Contains(pi.Name))
                            fieldNames.Add(pi.Name);

                        //add to primary key list
                        if (fatt.IsPrimaryKey)
                            if (!primaryKeys.Contains(pi.Name))
                                primaryKeys.Add(pi.Name);

                        if (fatt.IsAutoNumber)
                            if (!autoNumbers.Contains(pi.Name))
                                autoNumbers.Add(pi.Name);

                        //check to see if field is nullable
                        if (fatt.IsNullable || (pi.PropertyType.IsGenericType && pi.PropertyType.GetGenericTypeDefinition() == typeof(Nullable<>)))
                            if (!nullableFields.Contains(pi.Name))
                                nullableFields.Add(pi.Name);

                        //add data length
                        if (fatt.DataLength > 0 && fatt.DataLength < int.MaxValue)
                            if (!fieldLengths.ContainsKey(pi.Name))
                                fieldLengths.Add(pi.Name, fatt.DataLength);

                        //add field type
                        if (!fieldTypes.ContainsKey(pi.Name))
                            fieldTypes.Add(pi.Name, pi.PropertyType);

                        //add the db type
                        if (!dbTypes.ContainsKey(pi.Name))
                            dbTypes.Add(pi.Name, fatt.DbType);
                    }
                }
            }
        }
        #endregion
    }
}